package com.tnl.app.dao;

import java.util.Collections;
import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.tnl.app.entity.Company;
import com.tnl.app.entity.JobDesc;
import com.tnl.base.BaseDAO;


@Repository
public class CompanyDAO {
	private static final String SQL_GET_ID = "select * from tb_company  where id = ?";
	private static final String SQL_GET_NAME = "select * from tb_company  where Name = ?";
	private static final String SQL_GET_ALL = "select * from tb_company ";
	private static final String SQL_GET_ALL_CATEGORY = "select * from tb_company where domain in "
			+ "(select id from tb_domain where id = ? or parentId = ?)" ;
	private static final String SQL_GET_COMPANY_DOMAIN="select * from tb_company where domain in "
			+ "(select id from tb_domain where id = ? or parentId = ?)" ;
	private static final String SQL_GET_SUBDOMAIN = "select * from tb_company where domain in (";
	private static final String SQL_SAVE = "insert into tb_company(name,orgnizationCode,licensecode,"
			+ "contact, email,phone,address,logoicon,image,approved,createdate,domain) values(?,?,?,?,?,?,?,?,?,?,?,?)";
	
	private static final String SQL_UPDATE = "update tb_company set name=?,orgnizationCode=?,licensecode=?,"
			+ "contact=?, email=?,phone=?,address=?,logoicon=?,image=?,approved=?,createdate=?,domain=?  where id =?";
	
	protected static final String SQL_DELETE = "delete from tb_company where id=?";
	
	private static final String SQL_GET_ALL_OF_SUBDOMAIN = "select * from tb_company where domian = ?";
	
	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_company where 1=1 ";
	private static final String SQL_GET_ALL_WITH_LIMIT = "select * from tb_company where 1=1 ";
	
	public List<Company> getALL() {
		return BaseDAO.queryForList(Company.class, SQL_GET_ALL);
	}
	
	public List<Company> getDomainCompanies(int domainId) {
			//select * from tb_company where domainid in (select id from tb_domain where id = domainid or parentId = domainid) 
		return BaseDAO.queryForList(Company.class, SQL_GET_ALL_CATEGORY,domainId, domainId);
	}
	
	public List<Company> getCompanyByDomain(int domainId,int majorDomainId1) {
		return BaseDAO.queryForList(Company.class, SQL_GET_COMPANY_DOMAIN,majorDomainId1,domainId);
	}
	
	public List<Company> getList(String... domainIds) {

		if (domainIds != null) {
			StringBuilder sql = new StringBuilder(SQL_GET_SUBDOMAIN);
			boolean start = false;
			for (String id : domainIds) {
				if (start) {
					sql.append(",").append(id);
				} else {
					start = true;
					sql.append(id);
				}
			}
			sql.append(")");
			return BaseDAO.queryForList(Company.class, sql.toString());

		} else {
			return Collections.emptyList();
		}
	}
		
	public Company getById(int Id) {
		return  BaseDAO.queryForObject(Company.class, SQL_GET_ID, Id);
	}
	public Company getByName(String name){
		return  BaseDAO.queryForObject(Company.class, SQL_GET_NAME, name);
	}
	
	final String SQL_MATCH_NAME ="select * from tb_company  where 1=1";
	public List<Company> matchByName(String name){
		String sql=SQL_MATCH_NAME;
		if(name!=null){
			sql+=" and name like '%"+name+"%' order by id asc";
		}
		return BaseDAO.queryForList(Company.class, sql);
	}
	public int save(Company com){
		if (com == null)
		{
			return -1;
		}
		Long result = BaseDAO.save(SQL_SAVE, com.getName(),com.getOrgnizationCode(),com.getLicenseCode(),
				com.getContact(),com.getEmail(),com.getPhone(),com.getAddress(),com.getLogoIcon(),
				com.getImage(),com.getApproved(),com.getCreateDate(),com.getDomain());
		if(result != null){
			return result.intValue();
		}
		return -1;
		
	}
	
	public int update(Company com)
		{
		if (com ==null)
		{
			return -1;
		}
		int result = BaseDAO.execute(SQL_UPDATE, com.getName(),com.getOrgnizationCode(),com.getLicenseCode(),
				com.getContact(),com.getEmail(),com.getPhone(),com.getAddress(),com.getLogoIcon(),
				com.getImage(),com.getApproved(),com.getCreateDate(),com.getDomain(),com.getId());
		return  result;
	}

	public void deleteById(int Id) {
		int result = BaseDAO.execute(SQL_DELETE, Id);
	}
	
	public Long getCountOfAll(String name, String phoneNum, Integer status) {
		String sql = SQL_GET_ALL_COUNT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '" + name +"'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
			
		}
		if( status != null){
			sql+=" and approved =" +status;
		}
		
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}
	
	public List<Company> getAllWithLimit(String name, String phoneNum, Integer status, long start, long length) {
		String sql = SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '" + name +"'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
		}
		if( status != null){
			sql+=" and approved =" +status;
		}
		sql+=" LIMIT ?, ?";
		return BaseDAO.queryForList(Company.class, sql, start, length);
	}
	
	public int delete(String Id) {

		if (Id != null) {

			int result = BaseDAO.execute(SQL_DELETE, Id);
			return result;
		} else {
			return 0;
		}
	}

  public List<Company> getListByDomainIds(int... domainIds) {

      if (domainIds != null) {
        StringBuilder sql = new StringBuilder(SQL_GET_SUBDOMAIN);
        boolean start = false;
        for (int id : domainIds) {
          if (start) {
            sql.append(",").append(id);
          } else {
            start = true;
            sql.append(id);
          }
        }
        sql.append(")");
        return BaseDAO.queryForList(Company.class, sql.toString());

      } else {
        return Collections.emptyList();
      
    }
  }
  //and ? < a.expireddate
  private final String SQL_GET_COMPANY_LIST="select a.cityid,b.id,b.name,b.logoicon,b.domain from tb_jobdesc a,tb_company b where a.companyid=b.id and a.status=1 and a.platformid !=2  group by a.companyid order by a.jobcode desc limit ?,?";
  public List<Company> companyList(int offset, int pageSize){
	  String sql=SQL_GET_COMPANY_LIST;
	  return BaseDAO.queryForList(Company.class, sql,offset,pageSize);
  }
  private final String SQL_GET__INDEX_COMPANIES="select a.cityid,b.id,b.name,b.logoicon,b.domain from tb_jobdesc a,tb_company b where a.companyid=b.id and a.status=1 and a.platformid !=2 "
  		+ " and b.tags = ? ";
  public List<Company> indexCompanies(int offset, int pageSize,String companyName,String tags){
	  String sql = SQL_GET__INDEX_COMPANIES;
	  if(org.apache.commons.lang3.StringUtils.isNotEmpty(companyName)) {
		  sql += " and b.name like '%"+companyName+"%'";
	  }
	  sql += " group by a.companyid order by a.jobcode desc limit ?,?";
	  return BaseDAO.queryForList(Company.class, sql,
			  tags,offset,pageSize);
  }
 //? < expireddate
  final String SQL_GET_COMPANY_COUNT="SELECT * from tb_jobdesc where status=1 and platformid !=2  group by companyid";
  public int getTotalNumForCompany(){
		List<Company> list = BaseDAO.queryForList(Company.class,SQL_GET_COMPANY_COUNT);
		if(list==null){
			return 0;
		}
		return list.size();
  }
  final String SQL_INDEX_GET_COMPANY_COUNT="select b.* from tb_jobdesc a,tb_company b where a.companyid=b.id and a.status=1 and a.platformid !=2 and b.tags = ?";
  public int getTotalNumCompany(String companyName,String tags){
	  String sql = SQL_INDEX_GET_COMPANY_COUNT;
	  if(org.apache.commons.lang3.StringUtils.isNotEmpty(companyName)) {
		  sql += " and b.name like '%"+companyName+"%'";
	  }
	  sql += " group by a.companyid";
		List<Company> list = BaseDAO.queryForList(Company.class,sql,tags);
		if(list==null){
			return 0;
		}
		return list.size();
  }
  //
  final String SQL_GET_JOBDESC_BY_COMPANY="SELECT * from tb_jobdesc where status = 1 and platformid != 2 and companyid=? order by jobcode desc";
  public List<JobDesc> getJobdescByCompany(int companyId){
	  String sql=SQL_GET_JOBDESC_BY_COMPANY;
	  return BaseDAO.queryForList(JobDesc.class, sql, companyId);
  }
  
  
	
	/**
	 * index search company
	 */
	public int getTotalNumForSearchCompany(String searchName){
		final String SQL_GET_SEARCHCOMPANY_COUNT="select count(*) from tb_company where name like '%"+searchName+"%' LIMIT 0,1";
		List<Company> list=BaseDAO.queryForList(Company.class, SQL_GET_SEARCHCOMPANY_COUNT);
		if(list==null) {
			return 0;
		}
		return list.size();
	}
	
	public List<Company> searchCompany(int offset,int pageSize,String searchName){
		final String SQL_GET_SEARCHCOMPANY="select * from tb_company where name like '%"+searchName+"%' order by id DESC LIMIT ?,?";
		List<Company> list=BaseDAO.queryForList(Company.class, SQL_GET_SEARCHCOMPANY,offset,pageSize);
		return list;
	}

	final String SQL_GETMEDIALIST="select id as value,name from tb_company where tags = ?";
	public List<Company> getALLByTag(String string) {
		// TODO Auto-generated method stub
		return BaseDAO.queryForList(Company.class, SQL_GETMEDIALIST, string);
	}

	final String SQL_GETOPTIONS="select id,name from tb_company where 1=1 ";
	public List<Company> getOptions(String name) {
		// TODO Auto-generated method stub
		String sql = SQL_GETOPTIONS;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(name)) {
			sql += "and name like '%"+name+"%'";
		}
		System.out.println(sql);
		return BaseDAO.queryForList(Company.class, sql);
	}

	final String SQL_GETOPTIONS2="select id,name from tb_company where approved=1";
	public List<Company> getOptions2(String name,int i) {
		String sql = SQL_GETOPTIONS;
		if (i == 2) {
			sql += "and domain in (18,19,20,24)";
		} else if (i == 1){
			sql += "and domain in (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,21,22,23,25)";
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(name)) {
			sql += "and name like '%"+name+"%'";
		}
		System.out.println(sql);
		return BaseDAO.queryForList(Company.class, sql);
	}

}
